drop table jms_dws.dws_leave_message_receive_receive_agent_agg;
CREATE TABLE jms_dws.dws_leave_message_receive_receive_agent_agg (
                                                                     dt                     DATE COMMENT '日期',
                                                                     leave_message_type            int comment '留言类型:1、破损 2、延误 3、遗失 4、其他',
                                                                     receive_financial_center_code varchar(80) comment '接受网点代理区code',
                                                                     receive_agent_code            varchar(80) comment '接受网点大区code',
                                                                     receive_agent_name            varchar(80) comment '接受网点大区名称',
                                                                     receive_financial_center_name varchar(80) comment '接受网点代理区名称',
                                                                     message_cnt                   bigint comment '留言总数',
                                                                     receive_reply_cnt                     bigint comment '接受方已回复数',
                                                                     receive_unreply_cnt                   bigint comment '接受方未回复数',
                                                                     receive_end_cnt                       bigint comment '接受方已完结数',
                                                                     receive_reply_rate                    double comment '接受方回复率',
                                                                     receive_end_rate                      double comment '接受方完结率'
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `leave_message_type`,`receive_financial_center_code`)
COMMENT '留言接受时间接受代理区汇总'
PARTITION BY RANGE(`dt`)
(
START ("2022-04-01") END ("2022-05-26") EVERY (INTERVAL 1 day)
    )
DISTRIBUTED BY HASH(`receive_financial_center_code`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);
